/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.tspp.dao;

import com.tspp.common.ResourceManager;
import com.tspp.dao.dto.Exam;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * Concrete realization of examDao interface. Retrieve the connection from pool in every method
 * @author Maks
 */
public class ExamDaoImpl extends AbstractDao<Exam> implements ExamDao {
    /**
     * All finder methods in this class use this SELECT constant to build their queries
     */
    private static final String SQL_SELECT = "SELECT ID_EXAM, ID_SPECIALITY, SUBJECT, TYPE, MAX_POINTS, DATE_TIME, ADDRESS FROM " + getTableName() + "";
    /**
     * SQL INSERT statement for this table
     */
    private static final String SQL_INSERT = "INSERT INTO " + getTableName() + " ( ID_SPECIALITY, SUBJECT, TYPE, MAX_POINTS, DATE_TIME, ADDRESS ) VALUES (?, ?, ?, ?, ?, ? )";
    /**
     * SQL UPDATE statement for this table
     */
    private static final String SQL_UPDATE = "UPDATE " + getTableName() + " SET ID_SPECIALITY = ?, SUBJECT = ?, TYPE = ?, MAX_POINTS = ?, DATE_TIME = ?, ADDRESS = ? WHERE ID_EXAM = ?";
    /**
     * SQL DELETE statement for this table
     */
    private static final String SQL_DELETE = "DELETE FROM " + getTableName() + " WHERE ID_EXAM = ?";
    /**
     * Index of column ID_EXAM
     */
    private static final int COLUMN_ID_EXAM = 1;
    /**
     * Index of column ID_SPECIALITY
     */
    private static final int COLUMN_ID_SPECIALITY = 2;
    /**
     * Index of column SUBJECT
     */
    private static final int COLUMN_SUBJECT = 3;
    /**
     * Index of column TYPE
     */
    private static final int COLUMN_TYPE = 4;
    /**
     * Index of column MAX_POINTS
     */
    private static final int COLUMN_MAX_POINTS = 5;
    /**
     * Index of column DATE_TIME
     */
    private static final int COLUMN_DATE_TIME = 6;
    /**
     * Index of column ADDRESS
     */
    private static final int COLUMN_ADDRESS = 7;

    /**
     * Inserts a new row in the EXAM table.
     */
    public void insert(Exam dto) throws DaoException {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = ResourceManager.getConnection();
            stmt = conn.prepareStatement(SQL_INSERT);
            int index = 1;
            if (dto.getIdSpeciality() == -1) {
                stmt.setNull(index++, java.sql.Types.INTEGER);
            } else {
                stmt.setInt(index++, dto.getIdSpeciality());
            }
            stmt.setString(index++, dto.getSubject());
            stmt.setString(index++, dto.getType());
            if (dto.getMaxPoints() == -1) {
                stmt.setNull(index++, java.sql.Types.INTEGER);
            } else {
                stmt.setInt(index++, dto.getMaxPoints());
            }
            stmt.setDate(index++, dto.getDateTime() == null ? null : new java.sql.Date(dto.getDateTime().getTime()));
            stmt.setString(index++, dto.getAddress());
            //System.out.println("Executing " + SQL_INSERT + " with DTO: " + dto);
            stmt.executeUpdate();
        } catch (Exception _e) {
            throw new DaoException("Exception: " + _e.getMessage(), _e);
        } finally {
            ResourceManager.close(stmt);
            ResourceManager.close(conn);
        }
    }

    /**
     * Updates a single row in the EXAM table.
     */
    public void update(int exam, Exam dto) throws DaoException {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = ResourceManager.getConnection();
            //System.out.println("Executing " + SQL_UPDATE + " with DTO: " + dto);
            stmt = conn.prepareStatement(SQL_UPDATE);
            int index = 1;
            if (dto.getIdSpeciality() == -1) {
                stmt.setNull(index++, java.sql.Types.INTEGER);
            } else {
                stmt.setInt(index++, dto.getIdSpeciality());
            }

            stmt.setString(index++, dto.getSubject());
            stmt.setString(index++, dto.getType());
            if (dto.getMaxPoints() == -1) {
                stmt.setNull(index++, java.sql.Types.INTEGER);
            } else {
                stmt.setInt(index++, dto.getMaxPoints());
            }
            stmt.setDate(index++, dto.getDateTime() == null ? null : new java.sql.Date(dto.getDateTime().getTime()));
            stmt.setString(index++, dto.getAddress());
            stmt.setInt(index++, exam);
            stmt.executeUpdate();
        } catch (Exception _e) {
            throw new DaoException("Exception: " + _e.getMessage(), _e);
        } finally {
            ResourceManager.close(stmt);
            ResourceManager.close(conn);
        }
    }

    /**
     * Deletes a single row in the EXAM table.
     */
    public void delete(int exam) throws DaoException {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = ResourceManager.getConnection();
            //System.out.println("Executing " + SQL_DELETE + " with PK: " + pk);
            stmt = conn.prepareStatement(SQL_DELETE);
            stmt.setInt(1, exam);
            stmt.executeUpdate();
        } catch (Exception _e) {
            throw new DaoException("Exception: " + _e.getMessage(), _e);
        } finally {
            ResourceManager.close(stmt);
            ResourceManager.close(conn);
        }
    }

    /**
     * Returns all rows from the EXAM table that match the criteria 'ID_EXAM = :idExam'.
     */
    public Exam findByPrimaryKey(int idExam) throws DaoException {
        List<Exam> ex = findByDynamicSelect(SQL_SELECT + " WHERE ID_EXAM = ?", new Object[]{new Integer(idExam)});
        return ex.isEmpty() ? null : ex.get(0);
    }

    /**
     * Returns all rows from the EXAM table that match the criteria ''.
     */
    public List<Exam> findAll() throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " ORDER BY ID_EXAM", null);
    }

    /**
     * Returns all rows from the EXAM table that match the criteria 'ID_SPECIALITY = :idSpeciality'.
     */
    public List<Exam> findBySpeciality(int idSpeciality) throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE ID_SPECIALITY = ?", new Object[]{new Integer(idSpeciality)});
    }

    /**
     * Returns all rows from the EXAM table that match the criteria 'ID_EXAM = :idExam'.
     */
    public List<Exam> findWhereIdExamEquals(int idExam) throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE ID_EXAM = ? ORDER BY ID_EXAM", new Object[]{new Integer(idExam)});
    }

    /**
     * Returns all rows from the EXAM table that match the criteria 'SUBJECT = :subject'.
     */
    public List<Exam> findWhereSubjectEquals(String subject) throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE SUBJECT = ? ORDER BY SUBJECT", new Object[]{subject});
    }

    /**
     * Returns all rows from the EXAM table that match the criteria 'TYPE = :type'.
     */
    public List<Exam> findWhereTypeEquals(String type) throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE TYPE = ? ORDER BY TYPE", new Object[]{type});
    }

    /**
     * Returns all rows from the EXAM table that match the criteria 'DATE_TIME = :dateTime'.
     */
    public List<Exam> findWhereDateTimeEquals(Date dateTime) throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE DATE_TIME = ? ORDER BY DATE_TIME", new Object[]{dateTime == null ? null : new java.sql.Date(dateTime.getTime())});
    }

    /**
     * Returns all rows from the EXAM table that match the criteria 'ADDRESS = :address'.
     */
    public List<Exam> findWhereAddressEquals(String address) throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE ADDRESS = ? ORDER BY ADDRESS", new Object[]{address});
    }

    /**
     * Return table name this dao is operated with
     * @return String name of table
     */
    public static String getTableName() {
        return "EXAM";
    }

    /**
     * Implements abstract getDtoFrom of AbstractDao
     * @see AbstractDAO
     * @param rs
     * @return newly created transfer object
     * @throws SQLException
     */
    @Override
    protected Exam getDtoFrom(ResultSet rs) throws SQLException {
        Exam dto = new Exam();
        dto.setIdExam(rs.getInt(COLUMN_ID_EXAM));
        dto.setIdSpeciality(rs.getInt(COLUMN_ID_SPECIALITY));
        if (rs.wasNull()) {
            dto.setIdSpeciality(-1);
        }
        dto.setSubject(rs.getString(COLUMN_SUBJECT));
        dto.setType(rs.getString(COLUMN_TYPE));
        dto.setMaxPoints(rs.getInt(COLUMN_MAX_POINTS));
        if (rs.wasNull()) {
            dto.setMaxPoints(-1);
        }
        dto.setDateTime(rs.getDate(COLUMN_DATE_TIME));
        dto.setAddress(rs.getString(COLUMN_ADDRESS));
        return dto;
    }
}
